package com.etooling.framework.repository;


import com.etooling.framework.entity.Employee;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface EmployeeRepository extends BaseRepository<Employee> {
    @Query(nativeQuery = true, value = "select * from  framework.t_employee e where e.org_id=?2 and e.login_id=?1 and (status='启用' or status is null )")
    Employee getOneByLoginId(String loginId, String orgId);

    @Query(nativeQuery = true, value = "select * from  framework.t_employee e where  e.login_id=?1 ")
    Employee getOneByLoginId(String loginId);


    @Query(nativeQuery = true, value = "select * from  framework.t_employee e where e.org_id=?2 and e.mobile=?1 and (status='启用' or status is null )")
    Employee getOneByMobile(String mobile, String orgId);


    @Query(nativeQuery = true, value = "select * from  framework.t_employee e where  e.mobile=?1  and (status='启用' or status is null ) limit 1")
    Employee getOneByMobile(String mobile);


    @Query(nativeQuery = true, value = "select * from framework.t_employee e where e.open_id = ?1")
    Employee getByOpenId(String openId);

    @Query(value = "select employee_id from framework.t_employee_role where employee_id=?1 and role_id in(select id from framework.t_role where name ='质检人员') ", nativeQuery = true)
    String checkIsQcUser(String id);

    @Query(value = "select employee_id from framework.t_employee_role where employee_id=?1 and role_id in(select id from framework.t_role where name ='签收人员') ", nativeQuery = true)
    String checkIsRectipt(String id);

    @Query(value = "select * from framework.t_employee where open_id = ?1 ", nativeQuery = true)
    Employee getOneByOpenId(Object openid);

    @Query(value = "select employee_id from framework.t_employee_role where employee_id=?1 and role_id in(select id from framework.t_role where name ='采购经理') ", nativeQuery = true)
    String checkIsManager(String id);

    @Query(value = "select employee_id from framework.t_employee_role where employee_id=?1 and role_id in(select id from framework.t_role where name ='采购商企业负责人') ", nativeQuery = true)
    String checkIsPrBoss(String id);

    @Query(nativeQuery = true, value = "select * from framework.t_employee where id in (select employee_id from framework.t_employee_role where role_id in(select id from framework.t_role where name ='采购经理')) and company_id = ?1 ")
    List<Employee> getManagerList(String companyId);

    @Query(nativeQuery = true, value = "select * from framework.t_employee where id in (select employee_id from framework.t_employee_role where role_id in(select id from framework.t_role where name ='采购商企业负责人')) and company_id = ?1 ")
    List<Employee> getBossList(String companyId);

    @Query(nativeQuery = true, value = "select * from framework.t_employee where id in (select employee_id from framework.t_employee_role where role_id in (select id from framework.t_role where name = ?2 ) ) and company_id = ?1 ")
    List<Employee> getOneByCompanyAndRole(String companyId, String roleName);

    @Query(nativeQuery = true, value = "select ce.id from framework.t_employee e left join pm.t_craft_employee ce on ce.employee_id = e.id where ce.craft_type_id = ?2 and e.id = ?1  ")
    String getOneByCraftType(String employeeId, String id);

    @Query(value = "select employee_id from framework.t_employee_role where employee_id=?1 and role_id in(select id from framework.t_role where name ='仓库人员') ", nativeQuery = true)
    String checkIsWmsUser(String id);

    @Query(nativeQuery = true, value = "select employee_id from framework.t_employee_role where employee_id=?1 and role_id in(select id from framework.t_role where name = ?2) ")
    String getOneByRole(String loginUserId, String roleName);

    @Query(nativeQuery = true, value = "select * " +
            "from framework.t_employee e " +
            "where e.id in (select employee_id " +
            "               from framework.t_employee_role ro " +
            "               where role_id in " +
            "                     (select id from framework.t_role tr where tr.id = ro.role_id and tr.name = '技术设计人员')) ")
    List<Employee> findHandleUser();

    @Query(nativeQuery = true, value = "select * from framework.t_employee  where company_id=?2 and id in (select employee_id from framework.t_employee_role where role_id in( select id from framework.t_role where name =?1))")
    List<Employee> getListByRoleName(String roleName, String companyId);

    @Query(nativeQuery = true, value = "select employee_id from framework.t_employee_role where role_id in(select id from framework.t_role where name=?2 ) and employee_id =?1 limit 1")
    String checkIsOrderAuditUser(String loginUserid, String roleName);

    @Query(nativeQuery = true, value = "select * from framework.t_employee where company_id=?1")
    List<Employee> getEmployee(String orgId);

    @Query(nativeQuery = true, value = "select * FROM framework.t_employee where union_id = ?1 ")
    Employee getByUnionId(Object unionid);

    /**
     * 根据用户id获取他所在的部门的所有员工,以及他下属的所有部门和员工
     * 2023/7/6
     *
     * @param loginId
     * @param companyId
     * @return Employee>
     * @author dan
     **/
    @Query(nativeQuery = true, value = "SELECT e.id,e.name,e.mobile,e.org_id org_id,o.name org_name,o.parent_id parent_id " +
            "FROM framework.t_employee e left join framework.t_org o on e.org_id = o.id " +
            "WHERE e.status <> '禁用' " +
            "  AND e.company_id = ?2 " +
            "  AND e.org_id IN ( " +
            "    SELECT id " +
            "    FROM ( " +
            "             WITH RECURSIVE a AS ( " +
            "                 SELECT * " +
            "                 FROM framework.t_org " +
            "                 WHERE id = (select org_id from framework.t_employee where id=?1 ) AND company_id = ?2 " +
            "                 UNION ALL " +
            "                 SELECT d.* " +
            "                 FROM framework.t_org d " +
            "                          JOIN a ON a.id = d.parent_id " +
            "             ) " +
            "             SELECT id " +
            "             FROM a " +
            "         ) AS t " +
            ")")
    List<Map<String,String>> getDepartmentEmployeesByLoginId(String loginId, String companyId);

    @Query(nativeQuery = true, value = "select * from framework.t_employee where id in(select employee_id from framework.t_employee_platform_auth where platform_id=?1)")
    List<Employee> getEmployeeByPlatformAuthId(String platformAuthId);

    @Query(nativeQuery = true, value = "select * from framework.t_employee where (login_id =?1 or mobile=?1) and (pwd=?2 or pwd=?3) limit 1")
    Employee getEmployeeByLoginId(String username, String pwd, String md5Pwd);
}

